package com.javaDemo.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @Description: Excel单元格列合并处理策略
 */
public class ExcelColumnMergeStrategy implements CellWriteHandler {
    private static final Log logger = LogFactory.getLog(ExcelColumnMergeStrategy.class);

    //自定义合并单元格的列 如果想合并 第4列和第5列 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
    private List<CellLineRange> cellLineRangeList;

    //自定义合并单元格的开始的行  一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2  ;
    private int mergeRowIndex;

    // 缓存已合并的单元格区域，避免重复检查
    private final Map<String, Boolean> mergedCellCache = new HashMap<>();

    // 批量处理的合并操作队列，减少频繁的合并区域操作
    private final Map<Integer, Map<Integer, CellRangeAddress>> pendingMerges = new HashMap<>();

    public ExcelColumnMergeStrategy(int mergeRowIndex, List<CellLineRange> cellLineRangeList) {
        this.mergeRowIndex = mergeRowIndex;
        this.cellLineRangeList = cellLineRangeList;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
            List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 只处理数据行，跳过表头
        int curRowIndex = cell.getRowIndex();
        int curColIndex = cell.getColumnIndex();
        
        // 如果当前行小于合并起始行，则不处理
        if (curRowIndex < mergeRowIndex) {
            return;
        }
        
        // 检查当前列是否在需要合并的范围内
        for (CellLineRange range : cellLineRangeList) {
            // 如果当前列在合并范围内且不是第一列，则尝试与左侧单元格合并
            if (curColIndex > range.getFirstCol() && curColIndex <= range.getLastCol()) {
                mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
                break;
            }
        }
        
        // 如果是最后一个单元格，执行批量合并操作
        if (isLastCellInSheet(writeSheetHolder, curRowIndex, curColIndex)) {
            processPendingMerges(writeSheetHolder.getSheet());
        }
    }



    /**
     * @description 当前单元格向左合并
     */
    private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 生成缓存键
        String cacheKey = curRowIndex + "-" + curColIndex;
        
        // 检查是否已处理过该单元格
        if (mergedCellCache.containsKey(cacheKey)) {
            return;
        }
        
        // 获取单元格值（优化类型判断）
        String curValue = getCellStringValue(cell);
        if (curValue == null) {
            return; // 跳过空值或无法处理的类型
        }
        
        // 获取左侧单元格
        Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
        if (leftCell == null) {
            return; // 左侧单元格不存在
        }
        
        // 获取左侧单元格值
        String leftValue = getCellStringValue(leftCell);
        if (leftValue == null) {
            return; // 跳过空值或无法处理的类型
        }
        
        // 比较值是否相等
        if (Objects.equals(leftValue, curValue)) {
            // 将合并操作添加到待处理队列，而不是立即执行
            addToPendingMerges(writeSheetHolder.getSheet(), curRowIndex, curColIndex);
            
            // 标记为已处理
            mergedCellCache.put(cacheKey, true);
        }
    }
    
    /**
     * 获取单元格的字符串值，处理不同类型的单元格
     */
    private String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 对于数值类型，转换为字符串进行比较
                double numValue = cell.getNumericCellValue();
                // 检查是否为整数
                if (numValue == Math.floor(numValue)) {
                    return String.valueOf((int) numValue);
                }
                return String.valueOf(numValue);
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        return null;
                    }
                }
            case BLANK:
                return "";
            default:
                return null;
        }
    }
    
    /**
     * 添加到待处理的合并队列
     */
    private void addToPendingMerges(Sheet sheet, int rowIndex, int colIndex) {
        // 检查是否已有待处理的合并区域包含左侧单元格
        Map<Integer, CellRangeAddress> rowMerges = pendingMerges.computeIfAbsent(rowIndex, k -> new HashMap<>());
        
        // 查找左侧列的合并区域
        CellRangeAddress existingRange = null;
        for (int col = colIndex - 1; col >= 0; col--) {
            existingRange = rowMerges.get(col);
            if (existingRange != null) {
                break;
            }
        }
        
        if (existingRange != null) {
            // 扩展现有合并区域
            existingRange.setLastColumn(colIndex);
        } else {
            // 创建新的合并区域
            rowMerges.put(colIndex - 1, new CellRangeAddress(rowIndex, rowIndex, colIndex - 1, colIndex));
        }
    }
    
    /**
     * 处理所有待合并的单元格
     */
    private void processPendingMerges(Sheet sheet) {
        if (pendingMerges.isEmpty()) {
            return;
        }
        
        // 获取当前所有合并区域
        List<CellRangeAddress> existingMerges = sheet.getMergedRegions();
        Map<String, Integer> existingMergeMap = new HashMap<>();
        
        // 建立现有合并区域的索引
        for (int i = 0; i < existingMerges.size(); i++) {
            CellRangeAddress addr = existingMerges.get(i);
            String key = addr.getFirstRow() + "-" + addr.getFirstColumn() + "-" + 
                         addr.getLastRow() + "-" + addr.getLastColumn();
            existingMergeMap.put(key, i);
        }
        
        // 批量处理待合并区域
        for (Map<Integer, CellRangeAddress> rowMerges : pendingMerges.values()) {
            for (CellRangeAddress newRange : rowMerges.values()) {
                // 检查是否与现有合并区域重叠
                String rangeKey = newRange.getFirstRow() + "-" + newRange.getFirstColumn() + "-" + 
                                 newRange.getLastRow() + "-" + newRange.getLastColumn();
                
                // 检查是否已存在完全相同的合并区域
                Integer existingIndex = existingMergeMap.get(rangeKey);
                if (existingIndex != null) {
                    // 已存在相同的合并区域，跳过
                    continue;
                }
                
                // 检查是否与现有区域重叠
                boolean hasOverlap = false;
                for (CellRangeAddress existing : existingMerges) {
                    if (isOverlapping(existing, newRange)) {
                        hasOverlap = true;
                        // 添加日志警告
                        logger.warn("跳过重叠的合并区域: " + newRange + ", 与现有区域重叠: " + existing);
                        break;
                    }
                }
                
                // 只有在不重叠的情况下才添加新的合并区域
                if (!hasOverlap) {
                    sheet.addMergedRegion(newRange);
                    // 更新现有合并区域列表和索引，以便后续检查
                    existingMerges.add(newRange);
                    existingMergeMap.put(rangeKey, existingMerges.size() - 1);
                }
            }
        }
        
        // 清空待处理队列
        pendingMerges.clear();
        mergedCellCache.clear();
    }
    
    /**
     * 判断是否为工作表中的最后一个单元格
     */
    private boolean isLastCellInSheet(WriteSheetHolder writeSheetHolder, int rowIndex, int colIndex) {
        Sheet sheet = writeSheetHolder.getSheet();
        int lastRowNum = sheet.getLastRowNum();
        
        if (rowIndex < lastRowNum) {
            return false;
        }
        
        Row lastRow = sheet.getRow(lastRowNum);
        if (lastRow == null) {
            return true;
        }
        
        return colIndex >= lastRow.getLastCellNum() - 1;
    }
    
    /**
     * 检查两个合并区域是否重叠
     */
    private boolean isOverlapping(CellRangeAddress range1, CellRangeAddress range2) {
        // 检查行是否重叠
        boolean rowsOverlap = !(range1.getLastRow() < range2.getFirstRow() || 
                               range1.getFirstRow() > range2.getLastRow());
        
        // 检查列是否重叠
        boolean colsOverlap = !(range1.getLastColumn() < range2.getFirstColumn() || 
                               range1.getFirstColumn() > range2.getLastColumn());
        
        // 行和列都重叠才算区域重叠
        return rowsOverlap && colsOverlap;
    }

}